<?php

require_once("Dao.php");

/**
 * DAO de l'objet Facture ponctuelle.
 * 
 * @author Arthur
 */
class FacturePonctuelleDao extends Dao {

    protected static $tableName = "factures_ponctuelles";

    /**
     * Liste complète des factures
     */
    public static function getAll() {
        return parent::find(self::$tableName);
    }

    /**
     * Liste complète des factures par client
     */
    public static function getAllByClientId($id) {
        return parent::find(self::$tableName, "client_id='" . $id . "'");
    }

    /**
     * Recherche par id
     */
    public static function searchById($id, $asObject = FALSE) {
        $q                                   = "SELECT * FROM factures_ponctuelles fp "
                . "INNER JOIN lignes_factures_ponctuelles lfp ON fp.id = lfp.id_facture_ponctuelle "
                . "INNER JOIN vente_terminal_ponctuel vtp on lfp.id_terminal = vtp.id "
                . "INNER JOIN fiche_client fc on fp.id_client = fc.id "
                . "WHERE fp.id = $id;";
        $result                              = parent::query($q);
        //formattage du résultat
        $ret['id']                           = $id;
        $ret['id_client']                    = $result[0]['id_client'];
        $ret['create_date']                  = $result[0]['create_date'];
        $ret['date_envoi_mail']              = $result[0]['date_envoi_mail'];
        $ret['pdf']                          = $result[0]['pdf'];
        $ret['mode_paiement']                = $result[0]['mode_paiement_ponctuel'];
        $ret['is_avoir']                     = $result[0]['is_avoir'];
        $ret['client']['id']                 = $result[0]['id'];
        $ret['client']['nom']                = $result[0]['nom'];
        $ret['client']['prenom']             = $result[0]['prenom'];
        $ret['client']['adresse']            = $result[0]['adresse'];
        $ret['client']['cp']                 = $result[0]['cp'];
        $ret['client']['ville']              = $result[0]['ville'];
        $ret['client']['pays']               = $result[0]['pays'];
        $ret['client']['raison_social']      = $result[0]['raison_social'];
        $ret['client']['rcs']                = $result[0]['rcs'];
        $ret['client']['ape']                = $result[0]['ape'];
        $ret['client']['activite']           = $result[0]['activite'];
        $ret['client']['tel_fix']            = $result[0]['tel_fix'];
        $ret['client']['tel_fax']            = $result[0]['tel_fax'];
        $ret['client']['tel_mobile']         = $result[0]['tel_mobile'];
        $ret['client']['email']              = $result[0]['email'];
        $ret['client']['siret']              = $result[0]['siret'];
        $ret['client']['code_client_hto']    = $result[0]['code_client_hto'];
        $ret['client']['code_iban']          = $result[0]['code_iban'];
        $ret['client']['code_etablissement'] = $result[0]['code_etablissement'];
        $ret['client']['code_guichet']       = $result[0]['code_guichet'];
        $ret['client']['num_compte']         = $result[0]['num_compte'];
        $ret['client']['rib']                = $result[0]['rib'];

        foreach ($result as $key => $value) {
            $ret['lignes'][$key]['id_terminal']  = $value['id_terminal'];
            $ret['lignes'][$key]['prix_ha']      = $value['prix_ha'];
            $ret['lignes'][$key]['prix_0']       = $value['prix_0'];
            $ret['lignes'][$key]['libelle']      = $value['libelle'];
            $ret['lignes'][$key]['code_article'] = $value['code_article'];
            $ret['lignes'][$key]['quantite']     = $value['quantite'];
            $ret['lignes'][$key]['prix']         = $value['prix'];
        }
        return $asObject ? json_decode(json_encode($ret)) : $ret;
    }

    /**
     * Renvoie toutes les factures ponctuelles à ajouter au fichier sepa
     */
    public static function getFacturesForSepa() {
        $q       = "SELECT fp.id as facture_id, SUM(lfp.prix) AS prixHT, lfp.*, fc.* FROM factures_ponctuelles fp "
                . "INNER JOIN lignes_factures_ponctuelles lfp ON fp.id = lfp.id_facture_ponctuelle "
                . "INNER JOIN fiche_client fc on fp.id_client = fc.id "
                . "WHERE fp.sepa IS FALSE AND mode_paiement_ponctuel = 'prv' AND is_avoir IS FALSE AND date_envoi_mail IS NOT NULL "
                . "GROUP BY fp.id;";
        $results = parent::query($q);
        //formattage du résultat
        $ret     = array();
        foreach ($results as $key => $result) {
            $ret[$key]['id']                           = $result['facture_id'];
            $ret[$key]['prixHT']                       = $result['prixHT'];
            $ret[$key]['client']['id']                 = $result['id_client'];
            $ret[$key]['client']['nom']                = $result['nom'];
            $ret[$key]['client']['prenom']             = $result['prenom'];
            $ret[$key]['client']['adresse']            = $result['adresse'];
            $ret[$key]['client']['cp']                 = $result['cp'];
            $ret[$key]['client']['ville']              = $result['ville'];
            $ret[$key]['client']['pays']               = $result['pays'];
            $ret[$key]['client']['raison_social']      = $result['raison_social'];
            $ret[$key]['client']['rcs']                = $result['rcs'];
            $ret[$key]['client']['ape']                = $result['ape'];
            $ret[$key]['client']['activite']           = $result['activite'];
            $ret[$key]['client']['tel_fix']            = $result['tel_fix'];
            $ret[$key]['client']['tel_fax']            = $result['tel_fax'];
            $ret[$key]['client']['tel_mobile']         = $result['tel_mobile'];
            $ret[$key]['client']['email']              = $result['email'];
            $ret[$key]['client']['siret']              = $result['siret'];
            $ret[$key]['client']['code_client_hto']    = $result['code_client_hto'];
            $ret[$key]['client']['code_iban']          = $result['code_iban'];
            $ret[$key]['client']['code_etablissement'] = $result['code_etablissement'];
            $ret[$key]['client']['code_guichet']       = $result['code_guichet'];
            $ret[$key]['client']['num_compte']         = $result['num_compte'];
            $ret[$key]['client']['rib']                = $result['rib'];
            $ret[$key]['client']['swift']              = $result['swift'];
        }

        return json_decode(json_encode($ret));
    }

    /**
     * Renvoie toutes les factures ponctuelles en avoir à ajouter au fichier sepa
     */
    public static function getAvoirsForSepa() {
        $q       = "SELECT fp.id as facture_id, SUM(lfp.prix) AS prixHT, lfp.*, fc.* FROM factures_ponctuelles fp "
                . "INNER JOIN lignes_factures_ponctuelles lfp ON fp.id = lfp.id_facture_ponctuelle "
                . "INNER JOIN fiche_client fc on fp.id_client = fc.id "
                . "WHERE fp.sepa IS FALSE AND is_avoir IS TRUE AND date_envoi_mail IS NOT NULL "
                . "GROUP BY fp.id;";
        $results = parent::query($q);
        //formattage du résultat
        $ret     = array();
        foreach ($results as $key => $result) {
            $ret[$key]['id']                           = $result['facture_id'];
            $ret[$key]['prixHT']                       = $result['prixHT'];
            $ret[$key]['client']['id']                 = $result['id_client'];
            $ret[$key]['client']['nom']                = $result['nom'];
            $ret[$key]['client']['prenom']             = $result['prenom'];
            $ret[$key]['client']['adresse']            = $result['adresse'];
            $ret[$key]['client']['cp']                 = $result['cp'];
            $ret[$key]['client']['ville']              = $result['ville'];
            $ret[$key]['client']['pays']               = $result['pays'];
            $ret[$key]['client']['raison_social']      = $result['raison_social'];
            $ret[$key]['client']['rcs']                = $result['rcs'];
            $ret[$key]['client']['ape']                = $result['ape'];
            $ret[$key]['client']['activite']           = $result['activite'];
            $ret[$key]['client']['tel_fix']            = $result['tel_fix'];
            $ret[$key]['client']['tel_fax']            = $result['tel_fax'];
            $ret[$key]['client']['tel_mobile']         = $result['tel_mobile'];
            $ret[$key]['client']['email']              = $result['email'];
            $ret[$key]['client']['siret']              = $result['siret'];
            $ret[$key]['client']['code_client_hto']    = $result['code_client_hto'];
            $ret[$key]['client']['code_iban']          = $result['code_iban'];
            $ret[$key]['client']['code_etablissement'] = $result['code_etablissement'];
            $ret[$key]['client']['code_guichet']       = $result['code_guichet'];
            $ret[$key]['client']['num_compte']         = $result['num_compte'];
            $ret[$key]['client']['rib']                = $result['rib'];
            $ret[$key]['client']['swift']              = $result['swift'];
        }
        return json_decode(json_encode($ret));
    }

    /**
     * Insertion d'une facture ponctuelle
     * @param type $idClient
     * @param array $lignesFactures
     * @param bool $isAvoir
     * @return int id de la facture
     */
    public static function insertNewFacture($idClient, array $lignesFactures, $isAvoir = FALSE, $modePaiement = 'prv') {
        //création de la facture
        $q = "INSERT INTO factures_ponctuelles (id_client, is_avoir, mode_paiement_ponctuel) VALUES ( " . $idClient . ", " . ($isAvoir ? 'TRUE' : 'FALSE') . ", '" . $modePaiement . "' )";
        if (parent::exec($q)) {
            $factureId = parent::queryOne("SELECT last_insert_id() as id");
            //insertion des lignes
            foreach ($lignesFactures as $l) {
                $prix     = parent::queryOne('SELECT prix_0 FROM vente_terminal_ponctuel WHERE id = ' . $l['id_terminal']);
                $values[] = "( " . $factureId['id'] . ", " . $l['id_terminal'] . ", " . $l['quantite'] . ", " . round((int) $l['quantite'] * (float) $prix['prix_0'], 2) . " )";
            }
            $ql = 'INSERT INTO lignes_factures_ponctuelles (id_facture_ponctuelle, id_terminal, quantite, prix ) VALUES' . implode(',', $values);
            parent::exec($ql);
            return $factureId['id'];
        }
    }

    public static function exec($sql) {
        return parent::exec($sql);
    }

    /**
     * Supprime une facture ponctuelle 
     * @param stdclass $facture
     * @return boolean
     */
    public static function deleteFacture(stdclass $facture) {
        $q = "DELETE fp, lfp FROM factures_ponctuelles fp "
                . "INNER JOIN lignes_factures_ponctuelles lfp ON fp.id = lfp.id_facture_ponctuelle "
                . "WHERE fp.id = $facture->id;";

        return self::exec($q);
    }

}
